﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Library
{
    public class clQH
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public DataTable getdata(Int16 type, Int16 idttp)  // 0 neu lay toan bo - 1 neu chi lay nhung tinh/thanh pho dang hoat dong
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_qh_danhsach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@type", SqlDbType.SmallInt).Value = type;
                    sqlcmd.Parameters.Add("@idttp", SqlDbType.SmallInt).Value = idttp;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int add(Int16 idttp, string ten, Int16 vitri, Int16 hienthi, string tenkhongdau)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_qh_themmoi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idttp", SqlDbType.SmallInt).Value = idttp;
                    sqlcmd.Parameters.Add("@ten", SqlDbType.NVarChar, 150).Value = ten;
                    sqlcmd.Parameters.Add("@vitri", SqlDbType.SmallInt).Value = vitri;
                    sqlcmd.Parameters.Add("@hienthi", SqlDbType.SmallInt).Value = hienthi;
                    sqlcmd.Parameters.Add("@tenkhongdau", SqlDbType.NVarChar, 150).Value = tenkhongdau; 
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public int update(Int16 id,Int16 idttp,string ten,Int16 vitri,Int16 hienthi, string tenkhongdau)
        {
            int rtn = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_qh_capnhat", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id",SqlDbType.SmallInt).Value = id;
                    sqlcmd.Parameters.Add("@idttp",SqlDbType.SmallInt).Value = idttp;
                    sqlcmd.Parameters.Add("@ten",SqlDbType.NVarChar,150).Value = ten;
                    sqlcmd.Parameters.Add("@vitri",SqlDbType.SmallInt).Value = vitri;
                    sqlcmd.Parameters.Add("@hienthi",SqlDbType.SmallInt).Value = hienthi;
                    sqlcmd.Parameters.Add("@tenkhongdau", SqlDbType.NVarChar, 150).Value = tenkhongdau;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    rtn = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return rtn;
        }

        public DataTable chitiet(Int16 id)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_qh_chitiet", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@id", SqlDbType.SmallInt).Value = id;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }
    }
}
